package com.studentManagement.daoImpl;

import com.studentManagement.dao.ClassDao;
import com.studentManagement.po.Class;
import com.studentManagement.po.Student;
import com.studentManagement.utils.JdbcUtil;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/4/29 0029.
 */
public class ClassDaoImpl implements ClassDao {
    private Connection conn = JdbcUtil.getConnection();
    private ResultSet resultSet = null;
    private PreparedStatement preparedStatement = null;

    @Override//通过教师的id查询所管理班级的id
    public List<Class> queryClassByTeacher_id(String teacher_id) {
        List<Class> arrayList = new ArrayList<>();
        String sql = "SELECT * FROM classs WHERE class_teacher_id=?";
        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, teacher_id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Class class1 = new Class();
                class1.setClass_id(resultSet.getString(1));
                class1.setClass_name(resultSet.getString(2));
                class1.setClass_department_id(resultSet.getInt(3));
                class1.setClass_teacher_id(resultSet.getString(4));
                class1.setClass_reward_id(resultSet.getString(5));
                class1.setClass_enter_year(resultSet.getInt(6));
                arrayList.add(class1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeStatementAndResultSet(resultSet, preparedStatement);
            JdbcUtil.closeConnection(conn);
        }

        return arrayList;
    }

    @Override//查询所有的班级id
    public List<String> queryAllClass_id() {
        List<String> list=new ArrayList<String>();
        String sql="select class_id from classs";
        try {
            preparedStatement=conn.prepareStatement(sql);
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()){
                list.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeStatementAndResultSet(resultSet,preparedStatement);
            JdbcUtil.closeConnection(conn);
        }
        return list;
    }

    @Override//查询所有学生的详细信息（class_id)
    public List<Student> queryAllStudentDetail(String class_id) {
        String sql="select * from students WHERE student_class_id=?";
        List<Student> list=new ArrayList<>();
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setString(1,class_id);
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()){
                Student student=new Student();
                student.setStudent_id(resultSet.getString(1));
                student.setStudent_name(resultSet.getString(2));
                student.setStudent_key(resultSet.getString(3));
                student.setStudent_sex(resultSet.getString(4));
                student.setStudent_age(resultSet.getInt(5));
                student.setStudent_state(resultSet.getInt(6));
                student.setStudent_credit(resultSet.getDouble(7));
                student.setStudent_phone(resultSet.getString(8));
                student.setStudent_address(resultSet.getString(9));
                student.setStudent_qq(resultSet.getString(10));
                student.setStudent_nation(resultSet.getString(11));
                student.setStudent_dormitory_id(resultSet.getString(12));
                student.setStudent_class_id(resultSet.getString(13));
                student.setStudent_duty(resultSet.getString(14));
                student.setStudent_enter_year(resultSet.getString(15));
                student.setStudent_image_Path(resultSet.getString(16));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeStatementAndResultSet(resultSet,preparedStatement);
            JdbcUtil.closeConnection(conn);
        }
        return list;
    }

    @Override
    /**
    * 方法的功能描述：插入一条班级信息
    * @params：[cl]
    * @return: void */
    public void addClass(Class cl) throws SQLException {
        QueryRunner qr=new QueryRunner();
        String sql="INSERT INTO classs VALUES (?,?,?,?,?,?);";
        Object params[]={cl.getClass_id(),cl.getClass_name(),cl.getClass_department_id(),cl.getClass_teacher_id(),cl.getClass_reward_id(),cl.getClass_enter_year()};
        qr.insert(conn, sql, new ScalarHandler(),params);
        DbUtils.close(conn);
    }
}
